CREATE VIEW [dbo].[vExperienceRoster]
AS
SELECT
    [dbo].[CustomerExperience].[CustomerExperienceKey],
    [dbo].[CustomerExperience].[UserKey],
    [dbo].[vOfferedCustomerExperience].[ProgramKey],
    [dbo].[vOfferedCustomerExperience].[ProgramName],
    [dbo].[vOfferedCustomerExperience].[ExperienceDefinitionKey],
    [dbo].[vOfferedCustomerExperience].[ExperienceDefinitionName],
    [dbo].[vOfferedCustomerExperience].[OfferingKey],
    [dbo].[vOfferedCustomerExperience].[OfferingName],
    [dbo].[vOfferedCustomerExperience].[ContactKey] AS Provider,
    [dbo].[vOfferedCustomerExperience].[OfferingName] AS Name,
    [dbo].[vBoContact].[FullName],
    [dbo].[vBoContact].[SortName],
    ISNULL([dbo].[GradeRef].[GradeValue], [dbo].[vOfferedCustomerExperience].[OfferedCustomerExperienceGrade]) AS Grade,
    [dbo].[CustomerExperience].[CustomerExperienceStatusCode] AS StatusCode,
    [dbo].[CustomerExperienceStatusRef].[CustomerExperienceStatusName] AS StatusName,
    [dbo].[vOfferedCustomerExperience].[NumberOfUnits],
    [dbo].[vOfferedCustomerExperience].[OfferingDate] AS Date

FROM
    [dbo].[CustomerExperience]
        INNER JOIN [dbo].[vOfferedCustomerExperience] ON [dbo].[CustomerExperience].[CustomerExperienceKey] = [dbo].[vOfferedCustomerExperience].[OfferedCustomerExperienceKey]
        INNER JOIN [dbo].[vBoContact] ON [dbo].[CustomerExperience].[UserKey] = [dbo].[vBoContact].[ContactKey]
        INNER JOIN [dbo].[CustomerExperienceStatusRef] ON [dbo].[CustomerExperience].[CustomerExperienceStatusCode] = [dbo].[CustomerExperienceStatusRef].[CustomerExperienceStatusCode]
        LEFT JOIN [dbo].[GradeRef] ON [dbo].[vOfferedCustomerExperience].[OfferedCustomerExperienceGradeKey] = [dbo].[GradeRef].[GradeKey]

UNION ALL
SELECT
    [CustomerExperience1].[CustomerExperienceKey],
    [CustomerExperience1].[UserKey],
    [dbo].[vUnofferedCustomerExperience].[ProgramKey],
    [dbo].[vUnofferedCustomerExperience].[ProgramName],
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionKey],
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionName],
    NULL AS OfferingKey,
    NULL AS OfferingName,
    NULL AS Provider,
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionName] AS Name,
    [vBoContact1].[FullName],
    [vBoContact1].[SortName],
    [dbo].[vUnofferedCustomerExperience].[UnofferedCustomerExperienceGrade] AS Grade,
    [CustomerExperience1].[CustomerExperienceStatusCode] AS StatusCode,
    [CustomerExperienceStatusCodeRef1].[CustomerExperienceStatusName] AS StatusName,
    [dbo].[vUnofferedCustomerExperience].[NumberOfUnits],
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionDate] AS Date

FROM
    [dbo].[CustomerExperience] AS [CustomerExperience1]
        INNER JOIN [dbo].[vUnofferedCustomerExperience] ON [CustomerExperience1].[CustomerExperienceKey] = [dbo].[vUnofferedCustomerExperience].[UnofferedCustomerExperienceKey]
        INNER JOIN [dbo].[vBoContact] AS [vBoContact1] ON [CustomerExperience1].[UserKey] = [vBoContact1].[ContactKey]
        INNER JOIN [dbo].[CustomerExperienceStatusRef] AS [CustomerExperienceStatusCodeRef1] ON [CustomerExperience1].[CustomerExperienceStatusCode] = [CustomerExperienceStatusCodeRef1].[CustomerExperienceStatusCode]

UNION ALL
SELECT
    [CustomerExperience2].[CustomerExperienceKey],
    [CustomerExperience2].[UserKey],
    [dbo].[vUserDefinedCustomerExperience].[ProgramKey],
    [dbo].[vUserDefinedCustomerExperience].[ProgramName],
    NULL AS ExperienceDefinitionKey,
    NULL AS ExperienceDefinitionName,
    NULL AS OfferingKey,
    NULL AS OfferingName,
    NULL AS Provider,
    [dbo].[vUserDefinedCustomerExperience].[UserDefinedCustomerExperienceName] AS Name,
    [vBoContact2].[FullName],
    [vBoContact2].[SortName],
    NULL AS Grade,
    [CustomerExperience2].[CustomerExperienceStatusCode] AS StatusCode,
    [CustomerExperienceStatusCodeRef2].[CustomerExperienceStatusName] AS StatusName,
    [dbo].[vUserDefinedCustomerExperience].[UserDefinedCustomerExperienceUnits] AS NumberOfUnits,
    [dbo].[vUserDefinedCustomerExperience].[CompletionDate] AS Date

FROM
    [dbo].[CustomerExperience] AS [CustomerExperience2]
        INNER JOIN [dbo].[vUserDefinedCustomerExperience] ON [CustomerExperience2].[CustomerExperienceKey] = [dbo].[vUserDefinedCustomerExperience].[UserDefinedCustomerExperienceKey]
        INNER JOIN [dbo].[vBoContact] AS [vBoContact2] ON [CustomerExperience2].[UserKey] = [vBoContact2].[ContactKey]
        INNER JOIN [dbo].[CustomerExperienceStatusRef] AS [CustomerExperienceStatusCodeRef2] ON [CustomerExperience2].[CustomerExperienceStatusCode] = [CustomerExperienceStatusCodeRef2].[CustomerExperienceStatusCode]

GO